﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using IHISCETeim.Library;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using IHISCETeim.Model;
namespace IHISCETeim.DAL
{
    public class UAgentMacStockDAL
    {
        public static bool Add(UAgentMacStockInfo obj)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("UAgentMacStock_Add", obj.SerialNumber, obj.AgentName, obj.IntoStockDate, obj.IsInIHISCE, obj.CheckNew, obj.CheckNewDate, obj.MacType, obj.Timer, obj.Antifreeze, obj.Diesel, obj.AccepPlace, obj.ScanImg);
            try
            {

                //DbCommand dbCommand = DB.GetStoredProcCommand("UAgentMacStock_Add");
                //DB.AddInParameter(dbCommand, "SerialNumber", DbType.String, obj.SerialNumber);
                //DB.AddInParameter(dbCommand, "AgentName", DbType.String, obj.AgentName);
                //DB.AddInParameter(dbCommand, "IntoStockDate", DbType.String, obj.IntoStockDate);
                //DB.AddInParameter(dbCommand, "IntoStockDate", DbType.String, obj.IntoStockDate);

                DB.ExecuteScalar(dbCommand);
                    return true;
            }
            catch
            {
                return false;
            }
        }
        public static bool Delete(int UASID)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("UAgentMacStock_Delete", UASID);
            try
            {
                DB.ExecuteScalar(dbCommand);
                return true;
            }
            catch
            {
                return false;
            }
        }
        public static IDataReader GetById(int UASID)
	    {
		    Database DB = DatabaseFactory.CreateDatabase();		
		
            DbCommand dbCommand = DB.GetStoredProcCommand("UAgentMacStock_GetById", UASID);
            return DB.ExecuteReader(dbCommand);
	    }
        public static DataTableCollection GetPagination(string sqlwhere, int PageIndex, int PageSize, string Sort, ref int TotalCount)
	    {
		    Database DB = DatabaseFactory.CreateDatabase();
           
            DbCommand dbCommand = DB.GetStoredProcCommand("UAgentMacStock_GetPagination");
            DB.AddInParameter(dbCommand, "WhereSql", DbType.String, sqlwhere);
            DB.AddInParameter(dbCommand, "PageIndex", DbType.Int32,PageIndex);
            DB.AddInParameter(dbCommand, "PageSize", DbType.Int32,PageSize);
            DB.AddInParameter(dbCommand, "Sort", DbType.String,Sort);
            DB.AddOutParameter(dbCommand, "TotalCount", DbType.Int32,8);
            DataTableCollection dt = DB.ExecuteDataSet(dbCommand).Tables;
				
            TotalCount=Convert.ToInt32( DB.GetParameterValue(dbCommand,"TotalCount"));
            return dt;
	    }
        public static bool Update(UAgentMacStockInfo obj)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("UAgentMacStock_Update", obj.UASID, obj.SerialNumber, obj.AgentName, obj.IntoStockDate, obj.IsInIHISCE, obj.CheckNew, obj.CheckNewDate, obj.MacType, obj.Timer, obj.Antifreeze, obj.Diesel, obj.AccepPlace, obj.ScanImg);
            try
            {
                DB.ExecuteScalar(dbCommand);
                return true;
            }
            catch (Exception exc)
            {
                return false;
            }
        }

        public static DataTable GetMacStockList()
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("UAgentMacStock_GetMacStockList");
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }

        public static bool UAgentMacStockDetail_Add(UAgentMacStockDetailInfo obj)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("UAgentMacStockDetail_Add", obj.UASID, obj.CheckDate, obj.CheckLabel, obj.RealName, obj.Reamrk);
            try
            {
                DB.ExecuteScalar(dbCommand);
                return true;
            }
            catch (Exception exc)
            {
                return false;
            }
        }
        public static IDataReader UAgentMacStockDetail_GetALLByUASID(int UASID)
	    {
		    Database DB = DatabaseFactory.CreateDatabase();		
		
            DbCommand dbCommand = DB.GetStoredProcCommand("UAgentMacStockDetail_GetALLByUASID", UASID);
            return DB.ExecuteReader(dbCommand);
	    }

        public static bool UAgentMacStockDetail_Delete(int UASDID)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("UAgentMacStockDetail_Delete", UASDID);
            try
            {
                DB.ExecuteScalar(dbCommand);
                return true;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 点检完成报表中，用于获取经销商一段时间内已点检的列表
        /// </summary>
        /// <param name="AgentName"></param>
        /// <param name="fromdate"></param>
        /// <param name="todate"></param>
        /// <returns></returns>
        public static DataTableCollection UAgentMacStockDetail_GetAllCheckList(string AgentName, string fromdate, string todate)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("UAgentMacStockDetail_GetAllCheckList", AgentName, fromdate, todate);
            return DB.ExecuteDataSet(dbCommand).Tables;
        }

        #region 2014-11-18 新机入库检查
        /// <summary>
        /// 新机入库验收检查模板表
        /// </summary>
        /// <returns></returns>
        public static IDataReader UAgentMacNewTemplete_GetAll()
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("UAgentMacNewTemplete_GetAll");
            return DB.ExecuteReader(dbCommand);
        }
        /// <summary>
        /// 新增样机入库及NG项
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="listNG"></param>
        /// <returns></returns>
        public static int UAgentMacNewNG_Add(UAgentMacStockInfo obj,List<UAgentMacNewNGInfo> listNG)
        {

             Database DB = DatabaseFactory.CreateDatabase();
             using (DbConnection conn = DB.CreateConnection())
             {
                 conn.Open();
                 //创建事务
                 DbTransaction trans = conn.BeginTransaction();
                 try
                 {
                     //1.向主表代理商样机库存管制表 新增
                     DbCommand dbCommand = DB.GetStoredProcCommand("UAgentMacStock_Add", obj.SerialNumber, obj.AgentName, obj.IntoStockDate, obj.IsInIHISCE, obj.CheckNew, obj.CheckNewDate, obj.MacType, obj.Timer, obj.Antifreeze, obj.Diesel, obj.AccepPlace, obj.ScanImg);
                     int UASID=Convert.ToInt32( DB.ExecuteScalar(dbCommand, trans));//如果小于则表示错误
                     if (UASID < 0)
                         throw new Exception("新增样机库存出错,可能此机号已录过");

                     //2.添加NG项
                     foreach (UAgentMacNewNGInfo itm in listNG)
                     {
                         DbCommand dbCommandNG = DB.GetStoredProcCommand("UAgentMacNewNG_Add");
                         DB.AddInParameter(dbCommandNG, "UASID", DbType.Int32, UASID);
                         DB.AddInParameter(dbCommandNG, "TempID", DbType.Int32, itm.TempID);
                         DB.AddInParameter(dbCommandNG, "NGRecord", DbType.String, itm.NGRecord);
                         DB.AddInParameter(dbCommandNG, "NGSolution", DbType.String, itm.NGSolution);
                         DB.AddInParameter(dbCommandNG, "NGImg", DbType.String, itm.NGImg);
                         Convert.ToInt32(DB.ExecuteScalar(dbCommandNG,trans));
                     }

                     trans.Commit();
                     return UASID;
                 }
                 catch
                 {
                     trans.Rollback();
                     return 0;
                 }
                 finally
                 {
                     //关闭连接
                     conn.Close();
                 }
                
                 
             }        


        }

        public static bool UAgentMacNewNG_Update(UAgentMacStockInfo obj, List<UAgentMacNewNGInfo> listNG)
        {

            Database DB = DatabaseFactory.CreateDatabase();
            using (DbConnection conn = DB.CreateConnection())
            {
                conn.Open();
                //创建事务
                DbTransaction trans = conn.BeginTransaction();
                try
                {
                    //1.向主表代理商样机库存管制表 新增
                    DbCommand dbCommand = DB.GetStoredProcCommand("UAgentMacStock_Update", obj.UASID, obj.SerialNumber, obj.AgentName, obj.IntoStockDate, obj.IsInIHISCE, obj.CheckNew, obj.CheckNewDate, obj.MacType, obj.Timer, obj.Antifreeze, obj.Diesel, obj.AccepPlace, obj.ScanImg);
                    DB.ExecuteScalar(dbCommand, trans);//如果小于则表示错误
                   //1.1 删除NG项
                    DbCommand dbCommandDelete = DB.GetStoredProcCommand("UAgentMacNewNG_DeleteByUASID", obj.UASID);
                    DB.ExecuteScalar(dbCommandDelete, trans);

                    //2.添加NG项
                    foreach (UAgentMacNewNGInfo itm in listNG)
                    {
                        DbCommand dbCommandNG = DB.GetStoredProcCommand("UAgentMacNewNG_Add");
                        DB.AddInParameter(dbCommandNG, "UASID", DbType.Int32, obj.UASID);
                        DB.AddInParameter(dbCommandNG, "TempID", DbType.Int32, itm.TempID);
                        DB.AddInParameter(dbCommandNG, "NGRecord", DbType.String, itm.NGRecord);
                        DB.AddInParameter(dbCommandNG, "NGSolution", DbType.String, itm.NGSolution);
                        DB.AddInParameter(dbCommandNG, "NGImg", DbType.String, itm.NGImg);
                        Convert.ToInt32(DB.ExecuteScalar(dbCommandNG, trans));
                    }

                    trans.Commit();
                    return true;
                }
                catch
                {
                    trans.Rollback();
                    return false;
                }
                finally
                {
                    //关闭连接
                    conn.Close();
                }


            }


        }
        public static bool UAgentMacStock_UpdateOut(int UASID, bool state, string outdate)
        {
            try
            {
                Database DB = DatabaseFactory.CreateDatabase();
                DbCommand dbCommandNG = DB.GetStoredProcCommand("UAgentMacStock_UpdateOut");
                DB.AddInParameter(dbCommandNG, "UASID", DbType.Int32, UASID);
                DB.AddInParameter(dbCommandNG, "state", DbType.Boolean, state);
                DB.AddInParameter(dbCommandNG, "outdate", DbType.String, outdate);

                DB.ExecuteScalar(dbCommandNG);
                return true;
            }
            catch
            {
                return false;
            }
        }
        public static IDataReader UAgentMacNewNG_GetByUASID(int UASID)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("UAgentMacNewNG_GetByUASID", UASID);
            return DB.ExecuteReader(dbCommand);
        }
        #endregion

        #region 新机入库检查报表
        /// <summary>
        /// 新机入库检查品质统计表
        /// </summary>
        /// <param name="sqlwhere"></param>
        /// <returns></returns>
        public static DataTable UAgentMacNewNG_GetNGTotalReport(string sqlwhere)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("UAgentMacNewNG_GetNGTotalReport", sqlwhere);
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }
        /// <summary>
        /// 新机入库检查品质统计表，获取一个项的所对应的明细
        /// </summary>
        /// <param name="sqlwhere"></param>
        /// <returns></returns>
        public static DataTable UAgentMacNewNG_GetNGTotalReportDetail(string sqlwhere)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("UAgentMacNewNG_GetNGTotalReportDetail", sqlwhere);
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }
        /// <summary>
        /// 获取保养次数统计表
        /// </summary>
        /// <param name="sqlwhere"></param>
        /// <returns></returns>
        public static DataTable UMacKeep_GetKeepTimeReport(string sqlwhere)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("UMacKeep_GetKeepTimeReport", sqlwhere);
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }
        
        #endregion

        #region 2014-12-2 样机定期检查
        /// <summary>
        /// 样机定期检查模板
        /// </summary>
        /// <returns></returns>
        public static IDataReader UAgentMacStockDetailTemplete_GetAll()
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("UAgentMacStockDetailTemplete_GetAll");
            return DB.ExecuteReader(dbCommand);
        }
        public static IDataReader UAgentMacStockDetailNG_GetAllByUASDID(int UASDID)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("UAgentMacStockDetailNG_GetAllByUASDID", UASDID);
            return DB.ExecuteReader(dbCommand);
        }

        public static string UAgentMacStockDetailNG_Add(UAgentMacStockDetailInfo obj, List<UAgentMacStockDetailNGInfo> listNG)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            using (DbConnection conn = DB.CreateConnection())
            {
                conn.Open();
                //创建事务
                DbTransaction trans = conn.BeginTransaction();
                try
                {
                    //1.向主表代理商样机库存管制表 新增
                    DbCommand dbCommand = DB.GetStoredProcCommand("UAgentMacStockDetail_Add", obj.UASID, obj.CheckDate, obj.CheckLabel, obj.RealName, obj.Reamrk);
                    int UASDID = Convert.ToInt32(DB.ExecuteScalar(dbCommand, trans));//如果小于则表示错误
                    if (UASDID < 0)
                        throw new Exception("新增样机定检出错");

                    //2.添加NG项
                    foreach (UAgentMacStockDetailNGInfo itm in listNG)
                    {
                        DbCommand dbCommandNG = DB.GetStoredProcCommand("UAgentMacStockDetailNG_Add");
                        DB.AddInParameter(dbCommandNG, "UASDID", DbType.Int32, UASDID);
                        DB.AddInParameter(dbCommandNG, "TempID", DbType.Int32, itm.TempID);
                        DB.AddInParameter(dbCommandNG, "NGSolution", DbType.String, itm.NGSolution);
                        DB.AddInParameter(dbCommandNG, "NGImg", DbType.String, itm.NGImg);
                        Convert.ToInt32(DB.ExecuteScalar(dbCommandNG, trans));
                    }

                    trans.Commit();
                    return "";
                }
                catch(Exception exc)
                {
                    trans.Rollback();
                    return exc.Message;
                }
                finally
                {
                    //关闭连接
                    conn.Close();
                }


            }      
        }

        public static string UAgentMacStockDetailNG_Update(UAgentMacStockDetailInfo obj, List<UAgentMacStockDetailNGInfo> listNG)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            using (DbConnection conn = DB.CreateConnection())
            {
                conn.Open();
                //创建事务
                DbTransaction trans = conn.BeginTransaction();
                try
                {
                    //删除
                    DbCommand dbCommanddelete = DB.GetStoredProcCommand("UAgentMacStockDetail_Delete", obj.UASDID);
                    DB.ExecuteScalar(dbCommanddelete, trans);//如果小于则表示错误


                    //1.向主表代理商样机库存管制表 新增
                    DbCommand dbCommand = DB.GetStoredProcCommand("UAgentMacStockDetail_Add", obj.UASID, obj.CheckDate, obj.CheckLabel, obj.RealName, obj.Reamrk);
                    int UASDID = Convert.ToInt32(DB.ExecuteScalar(dbCommand, trans));//如果小于则表示错误
                    if (UASDID < 0)
                        throw new Exception("新增样机定检出错");

                    //2.添加NG项
                    foreach (UAgentMacStockDetailNGInfo itm in listNG)
                    {
                        DbCommand dbCommandNG = DB.GetStoredProcCommand("UAgentMacStockDetailNG_Add");
                        DB.AddInParameter(dbCommandNG, "UASDID", DbType.Int32, UASDID);
                        DB.AddInParameter(dbCommandNG, "TempID", DbType.Int32, itm.TempID);
                        DB.AddInParameter(dbCommandNG, "NGSolution", DbType.String, itm.NGSolution);
                        DB.AddInParameter(dbCommandNG, "NGImg", DbType.String, itm.NGImg);
                        Convert.ToInt32(DB.ExecuteScalar(dbCommandNG, trans));
                    }

                    trans.Commit();
                    return "";
                }
                catch (Exception exc)
                {
                    trans.Rollback();
                    return exc.Message;
                }
                finally
                {
                    //关闭连接
                    conn.Close();
                }


            }
        }
        #endregion
    }
}
